package cn.com.headfree.tjfx.dao.impl;

import cn.com.headfree.entity.*;
import cn.com.headfree.tjfx.dao.IStatisticalDao;
import cn.com.headfree.tjfx.dto.StaffRankDto;
import cn.com.headfree.tjfx.vo.zjrcVo;
import com.gta.edu.sdk.base.dao.impl.BaseDaoImpl;
import com.gta.edu.sdk.common.dto.Page;
import com.gta.edu.sdk.util.StringUtil;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.springframework.stereotype.Repository;

import javax.sql.RowSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

/**
 * @Description: TODO
 * @Author wcSun
 * @Date 2021/9/29
 **/
@Repository("statisticalDao")
public class StatisticalDaoImpl  extends BaseDaoImpl<TEmpBaseInfo> implements IStatisticalDao {

    private static final Logger LOG = LogManager.getLogger(StatisticalDaoImpl.class);

//    String sql = "SELECT \n" +
//            "\t\t\t (SELECT count(1) age30follow\n" +
//            "       FROM t_emp_base_info TEBI LEFT JOIN t_dept_emp_rela TDER ON TEBI.erp_no = TDER.erp_no LEFT JOIN t_org_dept TOD ON TOD.id = TDER.dept_id \n" +
//            "       WHERE 1=1 AND TEBI.del_sign = 0 AND TEBI.is_valid = 0 AND TOD.path LIKE '%10333706%' \n" +
//            "\t\t\t and TEBI.birth_date >=date_sub(CURRENT_DATE(),interval 31 year) \n" +
//            "\t\t\t ORDER BY birth_date desc) age30follow ,\n" +
//            "\t\t\t \n" +
//            "\t\t\t(SELECT count(1) age30above40\n" +
//            "       FROM t_emp_base_info TEBI LEFT JOIN t_dept_emp_rela TDER ON TEBI.erp_no = TDER.erp_no LEFT JOIN t_org_dept TOD ON TOD.id = TDER.dept_id \n" +
//            "       WHERE 1=1 AND TEBI.del_sign = 0 AND TEBI.is_valid = 0 AND TOD.path LIKE '%10333706%' \n" +
//            "\t\t\t and TEBI.birth_date <date_sub(CURRENT_DATE(),interval 31 year) \n" +
//            "\t\t\t and TEBI.birth_date >=date_sub(CURRENT_DATE(),interval 41 year) \n" +
//            "\t\t\t ORDER BY birth_date desc) age30above40,\n" +
//            "\t\t\t \n" +
//            "\t\t\t (SELECT count(1) age40above50\n" +
//            "       FROM t_emp_base_info TEBI LEFT JOIN t_dept_emp_rela TDER ON TEBI.erp_no = TDER.erp_no LEFT JOIN t_org_dept TOD ON TOD.id = TDER.dept_id \n" +
//            "       WHERE 1=1 AND TEBI.del_sign = 0 AND TEBI.is_valid = 0 AND TOD.path LIKE '%10333706%' \n" +
//            "\t\t\t and TEBI.birth_date <date_sub(CURRENT_DATE(),interval 41 year) \n" +
//            "\t\t\t and TEBI.birth_date >date_sub(CURRENT_DATE(),interval 51 year) \n" +
//            "\t\t\t ORDER BY birth_date desc) age40above50,\n" +
//            "\t\t\t \n" +
//            "\t\t\t (SELECT count(1) age51above\n" +
//            "       FROM t_emp_base_info TEBI LEFT JOIN t_dept_emp_rela TDER ON TEBI.erp_no = TDER.erp_no LEFT JOIN t_org_dept TOD ON TOD.id = TDER.dept_id \n" +
//            "       WHERE 1=1 AND TEBI.del_sign = 0 AND TEBI.is_valid = 0 AND TOD.path LIKE '%10333956%'  \n" +
//            "\t\t\t and TEBI.birth_date <=date_sub(CURRENT_DATE(),interval 51 year) \n" +
//            "\t\t\t ORDER BY birth_date desc) age51above";


    @Override
    public List<TOrgDept> getDeptsByOrgId(String orgId) {
        String sql = "select id,name,limit_start,limit_end,p_id,path,level from t_org_dept where 1=1 ";
        StringBuilder sqlbuilder = new StringBuilder(sql);
        ArrayList<Object> arrayList = new ArrayList<Object>();
        sqlbuilder.append(" AND p_id = ? ");
        arrayList.add(orgId);
        sqlbuilder.append(" order by level ,sort ");
        return this.findAllBySQL(TOrgDept.class, sqlbuilder.toString(), null, false, arrayList.toArray());
    }

    @Override
    public List<IscUser> searchUserList(String orgId){
        ArrayList<Object> arrayList = new ArrayList<>();
        StringBuilder sqlbuilder = new StringBuilder(" SELECT TEBI.id as ID,TEBI.NAME as NAME,TEBI.erp_no as FAX,TOD.id as dept_id,TOD.NAME as ORG_ANISATION_NAME,TDER.org_id as BASEORG_ID,TEBI.mobile_phone as TEL," +
                "TEBI.cur_post as TITLE ,DATE_FORMAT(TEBI.birth_date,'%Y-%m-%d %H:%i:%s') as birth_date,TEBI.sex sex_code");
        sqlbuilder.append(" FROM t_emp_base_info TEBI LEFT JOIN t_dept_emp_rela TDER ON TEBI.erp_no = TDER.erp_no LEFT JOIN t_org_dept TOD ON TOD.id = TDER.dept_id ");
        sqlbuilder.append(" WHERE 1=1 AND TEBI.del_sign = 0 AND TEBI.is_valid = 0 ");
        sqlbuilder.append(" AND TOD.path LIKE concat('%',?,'%') ");
        arrayList.add(orgId);
        sqlbuilder.append(" ORDER BY TEBI.sort ASC");
        return this.findAllBySQL(IscUser.class, sqlbuilder.toString(), null, false, arrayList.toArray());
    }

    @Override
    public TOrgDept getDeptsById(String id) {
        String sql = "select id,name,limit_start,limit_end,p_id,path,level from t_org_dept where 1=1 ";
        StringBuilder sqlbuilder = new StringBuilder(sql);
        ArrayList<Object> arrayList = new ArrayList<Object>();
        sqlbuilder.append(" AND id = ? ");
        arrayList.add(id);
        sqlbuilder.append(" order by level ,sort ");
        return this.findUnique(TOrgDept.class, sqlbuilder.toString() , arrayList.toArray());
    }

    @Override
    public List<HashMap> getGroupBySex(String orgId){
        String sql = "SELECT TEBI.sex sex ,count(1) count " +
                "    FROM t_emp_base_info TEBI " +
                "    LEFT JOIN t_dept_emp_rela TDER ON TEBI.erp_no = TDER.erp_no " +
                "    LEFT JOIN t_org_dept TOD ON TOD.id = TDER.dept_id " +
                "    WHERE 1=1 AND TEBI.del_sign = 0 AND TEBI.is_valid = 0 " ;
        StringBuilder sqlBuilder = new StringBuilder(sql);
        sqlBuilder.append(" AND TOD.path LIKE concat('%',?,'%') ");
        sqlBuilder.append( " GROUP BY TEBI.sex");
        List<Object> paramList = new ArrayList<Object>();
        paramList.add(orgId);
        return this.queryList(sqlBuilder,paramList);
        }


    @Override
    public List<TEducateInfo> searchEducate(String orgId){
        ArrayList<Object> arrayList = new ArrayList<>();
        StringBuilder sqlbuilder = new StringBuilder(" SELECT TEBI.erp_no,tab.educ_name");
        sqlbuilder.append(" FROM t_emp_base_info TEBI LEFT JOIN t_dept_emp_rela TDER ON TEBI.erp_no = TDER.erp_no LEFT JOIN t_org_dept TOD ON TOD.id = TDER.dept_id ");
        sqlbuilder.append(" LEFT JOIN (SELECT erp_no,min(educ_name) educ_name FROM t_educate_info where del_sign = 0 AND is_valid = 0  GROUP BY erp_no) tab on tab.erp_no = TEBI.erp_no ");
        sqlbuilder.append(" WHERE 1=1 AND TEBI.del_sign = 0 AND TEBI.is_valid = 0 ");
        sqlbuilder.append(" AND TOD.path LIKE concat('%',?,'%')  and tab.educ_name is not null ");
        arrayList.add(orgId);
        sqlbuilder.append(" ORDER BY TEBI.sort ASC");
        return this.findAllBySQL(TEducateInfo.class, sqlbuilder.toString(), null, false, arrayList.toArray());
    }


    @Override
    public List<TSkillLevelInfo> searchSkillLevel(String orgId){
        ArrayList<Object> arrayList = new ArrayList<>();
        StringBuilder sqlbuilder = new StringBuilder(" SELECT TEBI.erp_no,tab.qualifi_level");
        sqlbuilder.append(" FROM t_emp_base_info TEBI LEFT JOIN t_dept_emp_rela TDER ON TEBI.erp_no = TDER.erp_no LEFT JOIN t_org_dept TOD ON TOD.id = TDER.dept_id ");
        sqlbuilder.append(" LEFT JOIN (SELECT erp_no,min(qualifi_level) qualifi_level FROM t_skill_level_info where del_sign = 0 AND is_valid = 0  GROUP BY erp_no) tab on tab.erp_no = TEBI.erp_no ");
        sqlbuilder.append(" WHERE 1=1 AND TEBI.del_sign = 0 AND TEBI.is_valid = 0 ");
        sqlbuilder.append(" AND TOD.path LIKE concat('%',?,'%')  and tab.qualifi_level is not null ");
        arrayList.add(orgId);
        sqlbuilder.append(" ORDER BY TEBI.sort ASC");
        return this.findAllBySQL(TSkillLevelInfo.class, sqlbuilder.toString(), null, false, arrayList.toArray());
    }


    @Override
    public List<IscUser> searchCadreUserList(String orgId){
        ArrayList<Object> arrayList = new ArrayList<>();
        StringBuilder sqlbuilder = new StringBuilder(" SELECT TEBI.id as ID,TEBI.NAME as NAME,TEBI.erp_no as FAX,TOD.id as dept_id,TOD.NAME as ORG_ANISATION_NAME,TDER.org_id as BASEORG_ID,TEBI.post_level as post_level," +
                "DATE_FORMAT(TEBI.birth_date,'%Y-%m-%d %H:%i:%s') as birth_date,TEBI.sex sex_code");
        sqlbuilder.append(" FROM t_emp_base_info TEBI LEFT JOIN t_dept_emp_rela TDER ON TEBI.erp_no = TDER.erp_no LEFT JOIN t_org_dept TOD ON TOD.id = TDER.dept_id ");
        sqlbuilder.append(" WHERE 1=1 AND TEBI.del_sign = 0 AND TEBI.is_valid = 0 ");
        sqlbuilder.append(" AND TOD.path LIKE concat('%',?,'%') and TEBI.post_level is not null");
        arrayList.add(orgId);
        sqlbuilder.append(" ORDER BY TEBI.sort ASC");
        return this.findAllBySQL(IscUser.class, sqlbuilder.toString(), null, false, arrayList.toArray());
    }

    public List<HashMap> queryList(StringBuilder sqlBuilder,List<Object> paramList){
        List result = new ArrayList();
        try {
            RowSet rs = queryset(sqlBuilder.toString(), paramList.toArray());
            if (null != rs) {
                ResultSetMetaData metaData = rs.getMetaData();// 获得结果集结构信息（元数据）
                int columnNum = metaData.getColumnCount();// ResultSet列数
                while (rs.next()) {
                    HashMap<Object, Object> map = new HashMap<Object, Object>();
                    for (int i = 1; i <= columnNum; i++) {
                        // rs.getObject(i) 获得当前行某一列字段的值
                        map.put(metaData.getColumnName(i).toLowerCase(), rs.getObject(i));
                    }
                    result.add(map);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            LOG.error("查询出现了异常,查询语句：" + sqlBuilder.toString(), e);
        }
        return result;
    }

    @Override
    public List<HashMap> getGroupByEmployeeLevel(String orgId){
        String sql = "SELECT  GET_DIC('StaffLevel',TEBI.employee_level) as level ,count(1) count " +
                "    FROM t_emp_base_info TEBI " +
                "    LEFT JOIN t_dept_emp_rela TDER ON TEBI.erp_no = TDER.erp_no " +
                "    LEFT JOIN t_org_dept TOD ON TOD.id = TDER.dept_id " +
                "    WHERE 1=1 AND TEBI.del_sign = 0 AND TEBI.is_valid = 0 and TEBI.employee_level is not null" ;
        StringBuilder sqlBuilder = new StringBuilder(sql);
        sqlBuilder.append(" AND TOD.path LIKE concat('%',?,'%') ");
        sqlBuilder.append( "  GROUP BY TEBI.employee_level ORDER BY TEBI.employee_level ");
        List<Object> paramList = new ArrayList<Object>();
        paramList.add(orgId);
        return this.queryList(sqlBuilder,paramList);
    }

    @Override
    public void findEmployeeLevelList(Page<StaffRankDto> page,  StaffRankDto staffRankDto) {
        String sql = " SELECT TEBI.NAME as user_name,TEBI.erp_no as erp_no,TOD.NAME as org_name,TDER.org_id as org_ID,TEBI.employee_level as employee_level, " +
                " TEBI.birth_date,TEBI.sex sex,pro.subj_name,conteduc.grad_school cont_educ_school,conteduc.educ_name cont_educ,highesteduc.grad_school highest_educ_school,highesteduc.educ_name highest_educ " +
                " FROM t_emp_base_info TEBI " +
                " LEFT JOIN t_dept_emp_rela TDER ON TEBI.erp_no = TDER.erp_no " +
                " LEFT JOIN t_org_dept TOD ON TOD.id = TDER.org_id " +
                " left join (SELECT erp_no,min(CONCAT(subj_level,'|',subj_name)) subj_name from t_pro_tec_info  GROUP BY erp_no) pro on pro.erp_no = TEBI.erp_no " +
                " left join t_educate_info conteduc on conteduc.is_cont_educ=0 and conteduc.erp_no = TEBI.erp_no and conteduc.is_valid = '0' and conteduc.del_sign = '0' " +
                " left join t_educate_info highesteduc on highesteduc.is_highest_educ=0 and highesteduc.erp_no = TEBI.erp_no and highesteduc.is_valid = '0' and highesteduc.del_sign = '0'" +
                " WHERE 1=1 AND TEBI.del_sign = 0 AND TEBI.is_valid = 0  and TEBI.employee_level is not null ";
        StringBuilder sqlBuilder = new StringBuilder(sql);
        ArrayList<Object> arrayList = new ArrayList<Object>();
        if (!StringUtil.isNullOrEmpty(staffRankDto.getOrgId())) {
            sqlBuilder.append(" AND TOD.path LIKE concat('%',?,'%') ");
            arrayList.add(staffRankDto.getOrgId());
        }
        if (!StringUtil.isNullOrEmpty(staffRankDto.getUserName())) {
            sqlBuilder.append(" AND TEBI.NAME LIKE concat('%',?,'%') ");
            arrayList.add(staffRankDto.getUserName());
        }
        if (!StringUtil.isNullOrEmpty(staffRankDto.getSex())) {
            sqlBuilder.append(" AND TEBI.sex = ? ");
            arrayList.add(staffRankDto.getSex());
        }
        if (!StringUtil.isNullOrEmpty(staffRankDto.getEmployeeLevel())) {
            sqlBuilder.append(" AND TEBI.employee_level = ? ");
            arrayList.add(staffRankDto.getEmployeeLevel());
        }
        if (!StringUtil.isNullOrEmpty(staffRankDto.getContEduc())) {
            sqlBuilder.append(" AND conteduc.educ_name = ? ");
            arrayList.add(staffRankDto.getContEduc());
        }
        this.findPageBySql(StaffRankDto.class, sqlBuilder.toString(), page, arrayList.toArray());
    }

    @Override
    public List<StaffRankDto> findEmployeeLevelList(StaffRankDto staffRankDto) {
        String sql = " SELECT TEBI.NAME as user_name,TEBI.erp_no as erp_no,TOD.NAME as org_name,TDER.org_id as org_ID,TEBI.employee_level as employee_level, " +
                " TEBI.birth_date,TEBI.sex sex,pro.subj_name,conteduc.grad_school cont_educ_school,conteduc.educ_name cont_educ,highesteduc.grad_school highest_educ_school,highesteduc.educ_name highest_educ " +
                " FROM t_emp_base_info TEBI " +
                " LEFT JOIN t_dept_emp_rela TDER ON TEBI.erp_no = TDER.erp_no " +
                " LEFT JOIN t_org_dept TOD ON TOD.id = TDER.org_id " +
                " left join (SELECT erp_no,min(CONCAT(subj_level,'|',subj_name)) subj_name from t_pro_tec_info  GROUP BY erp_no) pro on pro.erp_no = TEBI.erp_no " +
                " left join t_educate_info conteduc on conteduc.is_cont_educ=0 and conteduc.erp_no = TEBI.erp_no and conteduc.is_valid = '0' and conteduc.del_sign = '0' " +
                " left join t_educate_info highesteduc on highesteduc.is_highest_educ=0 and highesteduc.erp_no = TEBI.erp_no and highesteduc.is_valid = '0' and highesteduc.del_sign = '0'" +
                " WHERE 1=1 AND TEBI.del_sign = 0 AND TEBI.is_valid = 0  and TEBI.employee_level is not null ";
        StringBuilder sqlBuilder = new StringBuilder(sql);
        ArrayList<Object> arrayList = new ArrayList<Object>();
        if (!StringUtil.isNullOrEmpty(staffRankDto.getOrgId())) {
            sqlBuilder.append(" AND TOD.path LIKE concat('%',?,'%') ");
            arrayList.add(staffRankDto.getOrgId());
        }
        if (!StringUtil.isNullOrEmpty(staffRankDto.getUserName())) {
            sqlBuilder.append(" AND TEBI.NAME LIKE concat('%',?,'%') ");
            arrayList.add(staffRankDto.getUserName());
        }
        if (!StringUtil.isNullOrEmpty(staffRankDto.getSex())) {
            sqlBuilder.append(" AND TEBI.sex = ? ");
            arrayList.add(staffRankDto.getSex());
        }
        if (!StringUtil.isNullOrEmpty(staffRankDto.getEmployeeLevel())) {
            sqlBuilder.append(" AND TEBI.employee_level = ? ");
            arrayList.add(staffRankDto.getEmployeeLevel());
        }
        if (!StringUtil.isNullOrEmpty(staffRankDto.getContEduc())) {
            sqlBuilder.append(" AND conteduc.educ_name = ? ");
            arrayList.add(staffRankDto.getContEduc());
        }
        return this.findAllBySQL(StaffRankDto.class, sqlBuilder.toString(),  null, false, arrayList.toArray());
    }
}
